﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace YJ.Data.MySql
{
    public class Documents : YJ.Data.Interface.IDocuments
    {
        private DBHelper dbHelper = new DBHelper();
        /// <summary>
        /// 构造函数
        /// </summary>
        public Documents()
        {
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.Documents实体类</param>
        /// <returns>操作所影响的行数</returns>
        public int Add(YJ.Data.Model.Documents model)
        {
            string sql = @"INSERT INTO documents
				(ID,DirectoryID,DirectoryName,Title,Source,Contents,Files,WriteTime,WriteUserID,WriteUserName,EditTime,EditUserID,EditUserName,ReadUsers,ReadCount) 
				VALUES(@ID,@DirectoryID,@DirectoryName,@Title,@Source,@Contents,@Files,@WriteTime,@WriteUserID,@WriteUserName,@EditTime,@EditUserID,@EditUserName,@ReadUsers,@ReadCount)";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = model.ID },
				new MySqlParameter("@DirectoryID", MySqlDbType.VarChar, 36){ Value = model.DirectoryID },
				new MySqlParameter("@DirectoryName", MySqlDbType.VarChar, 200){ Value = model.DirectoryName },
				new MySqlParameter("@Title", MySqlDbType.Text, -1){ Value = model.Title },
				new MySqlParameter("@Source", MySqlDbType.VarChar, 50){ Value = model.Source },
				new MySqlParameter("@Contents", MySqlDbType.LongText, -1){ Value = model.Contents },
				model.Files == null ? new MySqlParameter("@Files", MySqlDbType.LongText, -1) { Value = DBNull.Value } : new MySqlParameter("@Files", MySqlDbType.LongText, -1) { Value = model.Files },
				new MySqlParameter("@WriteTime", MySqlDbType.DateTime, -1){ Value = model.WriteTime },
				new MySqlParameter("@WriteUserID", MySqlDbType.VarChar, 36){ Value = model.WriteUserID },
				new MySqlParameter("@WriteUserName", MySqlDbType.VarChar, 50){ Value = model.WriteUserName },
				model.EditTime == null ? new MySqlParameter("@EditTime", MySqlDbType.DateTime, -1) { Value = DBNull.Value } : new MySqlParameter("@EditTime", MySqlDbType.DateTime, -1) { Value = model.EditTime },
				model.EditUserID == null ? new MySqlParameter("@EditUserID", MySqlDbType.VarChar, 36) { Value = DBNull.Value } : new MySqlParameter("@EditUserID", MySqlDbType.VarChar, 36) { Value = model.EditUserID },
				model.EditUserName == null ? new MySqlParameter("@EditUserName", MySqlDbType.VarChar, 50) { Value = DBNull.Value } : new MySqlParameter("@EditUserName", MySqlDbType.VarChar, 50) { Value = model.EditUserName },
				model.ReadUsers == null ? new MySqlParameter("@ReadUsers", MySqlDbType.LongText, -1) { Value = DBNull.Value } : new MySqlParameter("@ReadUsers", MySqlDbType.LongText, -1) { Value = model.ReadUsers },
				new MySqlParameter("@ReadCount", MySqlDbType.Int32, 11){ Value = model.ReadCount }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.Documents实体类</param>
        public int Update(YJ.Data.Model.Documents model)
        {
            string sql = @"UPDATE documents SET 
				DirectoryID=@DirectoryID,DirectoryName=@DirectoryName,Title=@Title,Source=@Source,Contents=@Contents,Files=@Files,WriteTime=@WriteTime,WriteUserID=@WriteUserID,WriteUserName=@WriteUserName,EditTime=@EditTime,EditUserID=@EditUserID,EditUserName=@EditUserName,ReadUsers=@ReadUsers,ReadCount=@ReadCount
				WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@DirectoryID", MySqlDbType.VarChar, 36){ Value = model.DirectoryID },
				new MySqlParameter("@DirectoryName", MySqlDbType.VarChar, 200){ Value = model.DirectoryName },
				new MySqlParameter("@Title", MySqlDbType.Text, -1){ Value = model.Title },
				new MySqlParameter("@Source", MySqlDbType.VarChar, 50){ Value = model.Source },
				new MySqlParameter("@Contents", MySqlDbType.LongText, -1){ Value = model.Contents },
				model.Files == null ? new MySqlParameter("@Files", MySqlDbType.LongText, -1) { Value = DBNull.Value } : new MySqlParameter("@Files", MySqlDbType.LongText, -1) { Value = model.Files },
				new MySqlParameter("@WriteTime", MySqlDbType.DateTime, -1){ Value = model.WriteTime },
				new MySqlParameter("@WriteUserID", MySqlDbType.VarChar, 36){ Value = model.WriteUserID },
				new MySqlParameter("@WriteUserName", MySqlDbType.VarChar, 50){ Value = model.WriteUserName },
				model.EditTime == null ? new MySqlParameter("@EditTime", MySqlDbType.DateTime, -1) { Value = DBNull.Value } : new MySqlParameter("@EditTime", MySqlDbType.DateTime, -1) { Value = model.EditTime },
				model.EditUserID == null ? new MySqlParameter("@EditUserID", MySqlDbType.VarChar, 36) { Value = DBNull.Value } : new MySqlParameter("@EditUserID", MySqlDbType.VarChar, 36) { Value = model.EditUserID },
				model.EditUserName == null ? new MySqlParameter("@EditUserName", MySqlDbType.VarChar, 50) { Value = DBNull.Value } : new MySqlParameter("@EditUserName", MySqlDbType.VarChar, 50) { Value = model.EditUserName },
				model.ReadUsers == null ? new MySqlParameter("@ReadUsers", MySqlDbType.LongText, -1) { Value = DBNull.Value } : new MySqlParameter("@ReadUsers", MySqlDbType.LongText, -1) { Value = model.ReadUsers },
				new MySqlParameter("@ReadCount", MySqlDbType.Int32, 11){ Value = model.ReadCount },
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = model.ID }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        public int Delete(Guid id)
        {
            string sql = "DELETE FROM documents WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = id.ToString() }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 将DataRedar转换为List
        /// </summary>
        private List<YJ.Data.Model.Documents> DataReaderToList(MySqlDataReader dataReader)
        {
            List<YJ.Data.Model.Documents> List = new List<YJ.Data.Model.Documents>();
            YJ.Data.Model.Documents model = null;
            while (dataReader.Read())
            {
                model = new YJ.Data.Model.Documents();
                model.ID = dataReader.GetString(0).ToGuid();
                model.DirectoryID = dataReader.GetString(1).ToGuid();
                model.DirectoryName = dataReader.GetString(2);
                model.Title = dataReader.GetString(3);
                model.Source = dataReader.GetString(4);
                model.Contents = dataReader.GetString(5);
                if (!dataReader.IsDBNull(6))
                    model.Files = dataReader.GetString(6);
                model.WriteTime = dataReader.GetDateTime(7);
                model.WriteUserID = dataReader.GetString(8).ToGuid();
                model.WriteUserName = dataReader.GetString(9);
                if (!dataReader.IsDBNull(10))
                    model.EditTime = dataReader.GetDateTime(10);
                if (!dataReader.IsDBNull(11))
                    model.EditUserID = dataReader.GetString(11).ToGuid();
                if (!dataReader.IsDBNull(12))
                    model.EditUserName = dataReader.GetString(12);
                if (!dataReader.IsDBNull(13))
                    model.ReadUsers = dataReader.GetString(13);
                model.ReadCount = dataReader.GetInt32(14);
                List.Add(model);
            }
            return List;
        }
        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<YJ.Data.Model.Documents> GetAll()
        {
            string sql = "SELECT * FROM documents";
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql);
            List<YJ.Data.Model.Documents> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        /// <summary>
        /// 查询记录数
        /// </summary>
        public long GetCount()
        {
            string sql = "SELECT COUNT(*) FROM documents";
            long count;
            return long.TryParse(dbHelper.GetFieldValue(sql), out count) ? count : 0;
        }
        /// <summary>
        /// 根据主键查询一条记录
        /// </summary>
        public YJ.Data.Model.Documents Get(Guid id)
        {
            string sql = "SELECT * FROM documents WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = id.ToString() }
			};
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<YJ.Data.Model.Documents> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 更新阅读次数+1
        /// </summary>
        /// <param name="id"></param>
        public void UpdateReadCount(Guid id)
        {
            string sql = "UPDATE Documents SET ReadCount=ReadCount+1 WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar){ Value = id }
			};
            dbHelper.Execute(sql, parameters);
        }

        /// <summary>
        /// 得到一页列表
        /// </summary>
        /// <param name="dirID">栏目ID，多个栏目逗号分开</param>
        /// <param name="pager"></param>
        /// <param name="query"></param>
        /// <param name="title"></param>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <returns></returns>
        public DataTable GetList(out string pager, string dirID, string userID, string query = "", string title = "", string date1 = "", string date2 = "", bool isNoRead = false)
        {
            List<MySqlParameter> parList = new List<MySqlParameter>();
            StringBuilder sql = new StringBuilder(@"SELECT ID,DirectoryID,DirectoryName,Title,WriteTime,WriteUserName,IsRead,ReadCount FROM Documents a LEFT JOIN DocumentsReadUsers b ON a.ID=b.DocumentID WHERE b.UserID=@UserID");
            parList.Add(new MySqlParameter("@UserID", MySqlDbType.VarChar) { Value = userID.ToGuid() });
            if (isNoRead)
            {
                sql.Append(" AND IsRead=0");
            }
            if (!dirID.IsNullOrEmpty())
            {
                sql.Append(" AND DirectoryID IN(" + Utility.Tools.GetSqlInString(dirID) + ")");
            }
            else
            {
                sql.Append(isNoRead ? " AND IsRead=0" : " AND 1=0");
            }
            if (!title.IsNullOrEmpty())
            {
                sql.Append(" AND INSTR(Title,@Title)>0");
                parList.Add(new MySqlParameter("@Title", MySqlDbType.VarChar) { Value = title });
            }
            if (date1.IsDateTime())
            {
                sql.Append(" AND WriteTime>=@WriteTime");
                parList.Add(new MySqlParameter("@WriteTime", MySqlDbType.DateTime) { Value = date1.ToDateTime().Date });
            }
            if (date2.IsDateTime())
            {
                sql.Append(" AND WriteTime<=@WriteTime1");
                parList.Add(new MySqlParameter("@WriteTime1", MySqlDbType.DateTime) { Value = date2.ToDateTime().AddDays(1).Date });
            }

            sql.Append(" ORDER BY WriteTime DESC");

            long count;
            int size = YJ.Utility.Tools.GetPageSize();
            int number = YJ.Utility.Tools.GetPageNumber();
            string sql1 = dbHelper.GetPaerSql(sql.ToString(), size, number, out count, parList.ToArray());
            pager = YJ.Utility.Tools.GetPagerHtml(count, size, number, query);
            //System.Web.HttpContext.Current.Response.Write(sql1);
            return dbHelper.GetDataTable(sql1, parList.ToArray());
        }

        /// <summary>
        /// 得到一页列表
        /// </summary>
        /// <param name="dirID">栏目ID，多个栏目逗号分开</param>
        /// <param name="pager"></param>
        /// <param name="query"></param>
        /// <param name="title"></param>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <returns></returns>
        public DataTable GetList(out long count, int size, int number, string dirID, string userID, string title = "", string date1 = "", string date2 = "", bool isNoRead = false, string order = "")
        {
            List<MySqlParameter> parList = new List<MySqlParameter>();
            StringBuilder sql = new StringBuilder(@"SELECT ID,DirectoryID,DirectoryName,Title,WriteTime,WriteUserName,IsRead,ReadCount FROM Documents a LEFT JOIN DocumentsReadUsers b ON a.ID=b.DocumentID WHERE b.UserID=@UserID");
            parList.Add(new MySqlParameter("@UserID", MySqlDbType.VarChar) { Value = userID.ToGuid() });
            if (isNoRead)
            {
                sql.Append(" AND IsRead=0");
            }
            if (!dirID.IsNullOrEmpty())
            {
                sql.Append(" AND DirectoryID IN(" + Utility.Tools.GetSqlInString(dirID) + ")");
            }
            else
            {
                sql.Append(isNoRead ? " AND IsRead=0" : " AND 1=0");
            }
            if (!title.IsNullOrEmpty())
            {
                sql.Append(" AND INSTR(Title,@Title)>0");
                parList.Add(new MySqlParameter("@Title", MySqlDbType.VarChar) { Value = title });
            }
            if (date1.IsDateTime())
            {
                sql.Append(" AND WriteTime>=@WriteTime");
                parList.Add(new MySqlParameter("@WriteTime", MySqlDbType.DateTime) { Value = date1.ToDateTime().Date });
            }
            if (date2.IsDateTime())
            {
                sql.Append(" AND WriteTime<=@WriteTime1");
                parList.Add(new MySqlParameter("@WriteTime1", MySqlDbType.DateTime) { Value = date2.ToDateTime().AddDays(1).Date });
            }
            sql.Append(" ORDER BY " + (order.IsNullOrEmpty() ? "WriteTime DESC" : order));
            string sql1 = dbHelper.GetPaerSql(sql.ToString(), size, number, out count, parList.ToArray());
            //System.Web.HttpContext.Current.Response.Write(sql1);
            return dbHelper.GetDataTable(sql1, parList.ToArray());
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        public int DeleteByDirectoryID(Guid directoryID)
        {
            string sql = "DELETE FROM Documents WHERE DirectoryID=@DirectoryID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@DirectoryID", MySqlDbType.VarChar){ Value = directoryID }
			};
            return dbHelper.Execute(sql, parameters);
        }
    }
}